package com.tianpengcc.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.tianpengcc.dao.DirayTypeDAO;
import com.tianpengcc.model.DirayType;
import com.tianpengcc.model.custom.DirayTypeCustom;
import com.tianpengcc.util.DBUtil;

public class DirayTypeDAOImpl implements DirayTypeDAO {
	private Connection conn ;
	
	@Override
	public List<DirayTypeCustom> findDirayCountAndDirayType() throws Exception {
		List<DirayTypeCustom> list = new ArrayList<DirayTypeCustom>();
		this.conn = DBUtil.getConnection() ;
		String sql = "SELECT dt.`dirayTypeId`,dt.`dirayTypeName`,COUNT(d.`dirayTypeId`) FROM t_diraytype dt LEFT JOIN t_diray d ON dt.`dirayTypeId` = d.`dirayTypeId` GROUP BY dt.`dirayTypeId`" ;
		Statement statement = conn.createStatement();
		ResultSet rs = statement.executeQuery(sql);
		DirayTypeCustom custom = null ;
		while(rs.next()){
			custom = new DirayTypeCustom() ;
			custom.setDirayTypeId(rs.getInt(1));
			custom.setDirayTypeName(rs.getString(2));
			custom.setDirayCount(rs.getInt(3));
			list.add(custom) ;
		}
		rs.close();
		statement.close();
		conn.close();
		return list;
	}

	@Override
	public int findCountByDirayTypeId(Integer dirayTypeId) throws Exception {
		String sql = "SELECT COUNT(*) FROM t_diray LEFT JOIN t_diraytype ON t_diray.`dirayTypeId` = t_diraytype.`dirayTypeId` WHERE t_diraytype.`dirayTypeId` = ?" ; 
		conn = DBUtil.getConnection();
		PreparedStatement ppst = conn.prepareStatement(sql);
		ppst.setInt(1, dirayTypeId);
		ResultSet rs = ppst.executeQuery();
		int count = 0 ;
		if(rs.next()){
			count = rs.getInt(1) ;
		}
		rs.close();
		ppst.close();
		
		DBUtil.close(conn);
		
		return count;
	}

	@Override
	public List<DirayType> findAll() throws Exception {
		List<DirayType> list = new ArrayList<DirayType>();
		this.conn = DBUtil.getConnection() ;
		String sql = "SELECT * FROM t_diraytype" ;
		Statement statement = conn.createStatement();
		ResultSet rs = statement.executeQuery(sql);
		DirayType dirayType = null ;
		while(rs.next()){
			dirayType = new DirayType() ;
			dirayType.setDirayTypeId(rs.getInt(1));
			dirayType.setDirayTypeName(rs.getString(2));
			list.add(dirayType) ;
		}
		rs.close();
		statement.close();
		conn.close();
		return list;
	}

	@Override
	public void doCreate(DirayType dirayType) throws Exception {
		String sql = "INSERT INTO t_diraytype VALUES(NULL,?)" ; 
		conn = DBUtil.getConnection();
		PreparedStatement ppst = conn.prepareStatement(sql);
		ppst.setString(1, dirayType.getDirayTypeName());
		int record = ppst.executeUpdate();
		if(record <= 0){
			throw new Exception("日记类型未保存成功！") ;
		}
		
		ppst.close();
		DBUtil.close(conn);
	}

	@Override
	public void doUpdate(Integer dirayTypeId, DirayType dirayType)
			throws Exception {
		String sql = "UPDATE t_diraytype SET dirayTypeName = ? WHERE dirayTypeId = ?" ; 
		conn = DBUtil.getConnection();
		PreparedStatement ppst = conn.prepareStatement(sql);
		ppst.setString(1, dirayType.getDirayTypeName());
		ppst.setInt(2, dirayTypeId);
		int record = ppst.executeUpdate();
		if(record <= 0){
			throw new Exception("日记类型未更新成功！") ;
		}
		
		ppst.close();
		DBUtil.close(conn);
	}

	@Override
	public void doDelete(Integer dirayTypeId) throws Exception {
		String sql = "DELETE FROM t_diraytype WHERE dirayTypeId = ?" ; 
		conn = DBUtil.getConnection();
		PreparedStatement ppst = conn.prepareStatement(sql);
		ppst.setInt(1, dirayTypeId);
		int record = ppst.executeUpdate();
		if(record <= 0){
			throw new Exception("日记类型未删除成功！") ;
		}
		
		ppst.close();
		DBUtil.close(conn);
	}

	@Override
	public DirayType findById(Integer dirayTypeId) throws Exception {
		DirayType dirayType = null ;
		this.conn = DBUtil.getConnection() ;
		String sql = "SELECT * FROM t_diraytype WHERE dirayTypeId = ?" ;
		PreparedStatement ppst = conn.prepareStatement(sql);
		ppst.setInt(1, dirayTypeId);
		ResultSet rs = ppst.executeQuery();
		while(rs.next()){
			dirayType = new DirayType() ;
			dirayType.setDirayTypeId(rs.getInt(1));
			dirayType.setDirayTypeName(rs.getString(2));
		}
		rs.close();
		ppst.close();
		conn.close();
		return dirayType;
	}

}
